{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT from Nobel\n",
    "\n",
    "## `nobel` Nobel Laureates\n",
    "\n",
    "We continue practicing simple SQL queries on a single table.\n",
    "\n",
    "This tutorial is concerned with a table of Nobel prize winners:\n",
    "\n",
    "```\n",
    "nobel(yr, subject, winner)\n",
    "```\n",
    "\n",
    "Using the `SELECT` statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Winners from 1950\n",
    "\n",
    "Change the query shown so that it displays Nobel prizes for 1950."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "nobel = pd.read_sql_table('nobel', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>1950</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>Kurt Alder</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>638</th>\n",
       "      <td>1950</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>Otto Diels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>639</th>\n",
       "      <td>1950</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Bertrand Russell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>640</th>\n",
       "      <td>1950</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Philip S. Hench</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641</th>\n",
       "      <td>1950</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Edward C. Kendall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>1950</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Tadeus Reichstein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>643</th>\n",
       "      <td>1950</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Ralph Bunche</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>1950</td>\n",
       "      <td>Physics</td>\n",
       "      <td>Cecil Powell</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr     subject             winner\n",
       "637  1950   Chemistry         Kurt Alder\n",
       "638  1950   Chemistry         Otto Diels\n",
       "639  1950  Literature   Bertrand Russell\n",
       "640  1950    Medicine    Philip S. Hench\n",
       "641  1950    Medicine  Edward C. Kendall\n",
       "642  1950    Medicine  Tadeus Reichstein\n",
       "643  1950       Peace       Ralph Bunche\n",
       "644  1950     Physics       Cecil Powell"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[nobel['yr']==1950, :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 1962 Literature\n",
    "\n",
    "Show who won the 1962 prize for Literature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>556</th>\n",
       "      <td>John Steinbeck</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             winner\n",
       "556  John Steinbeck"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[(nobel['yr']==1962) & (nobel['subject']=='Literature'), ['winner']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Albert Einstein\n",
    "\n",
    "Show the year and subject that won 'Albert Einstein' his prize."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>792</th>\n",
       "      <td>1921</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr  subject\n",
       "792  1921  Physics"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[nobel['winner']=='Albert Einstein', ['yr', 'subject']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Recent Peace Prizes\n",
    "\n",
    "Give the name of the 'Peace' winners since the year 2000, including 2000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Tunisian National Dialogue Quartet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Kailash Satyarthi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Malala Yousafzai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>European Union</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Ellen Johnson Sirleaf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Leymah Gbowee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Tawakel Karman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Liu Xiaobo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Barack Obama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Martti Ahtisaari</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>Intergovernmental Panel on Climate Change</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>Al Gore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>Grameen Bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Muhammad Yunus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>International Atomic Energy Agency</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Mohamed ElBaradei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Wangari Maathai</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Shirin Ebadi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Jimmy Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>United Nations</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>Kofi Annan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Kim Dae-jung</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        winner\n",
       "7           Tunisian National Dialogue Quartet\n",
       "18                           Kailash Satyarthi\n",
       "19                            Malala Yousafzai\n",
       "42                              European Union\n",
       "52                       Ellen Johnson Sirleaf\n",
       "53                               Leymah Gbowee\n",
       "54                              Tawakel Karman\n",
       "66                                  Liu Xiaobo\n",
       "78                                Barack Obama\n",
       "90                            Martti Ahtisaari\n",
       "102  Intergovernmental Panel on Climate Change\n",
       "103                                    Al Gore\n",
       "111                               Grameen Bank\n",
       "112                             Muhammad Yunus\n",
       "123         International Atomic Energy Agency\n",
       "124                          Mohamed ElBaradei\n",
       "136                            Wangari Maathai\n",
       "147                               Shirin Ebadi\n",
       "160                               Jimmy Carter\n",
       "174                             United Nations\n",
       "175                                 Kofi Annan\n",
       "188                               Kim Dae-jung"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[(nobel['yr']>=2000) & (nobel['subject']=='Peace'), ['winner']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Literature in the 1980's\n",
    "\n",
    "Show all details **(yr, subject, winner)** of the Literature prize winners for 1980 to 1989 inclusive."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>1989</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Camilo José Cela</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>310</th>\n",
       "      <td>1988</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Naguib Mahfouz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>1987</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Joseph Brodsky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>1986</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Wole Soyinka</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>341</th>\n",
       "      <td>1985</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Claude Simon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347</th>\n",
       "      <td>1984</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Jaroslav Seifert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>1983</td>\n",
       "      <td>Literature</td>\n",
       "      <td>William Golding</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>363</th>\n",
       "      <td>1982</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Gabriel García Márquez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>373</th>\n",
       "      <td>1981</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Elias Canetti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>384</th>\n",
       "      <td>1980</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Czeslaw Milosz</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr     subject                  winner\n",
       "299  1989  Literature        Camilo José Cela\n",
       "310  1988  Literature          Naguib Mahfouz\n",
       "322  1987  Literature          Joseph Brodsky\n",
       "331  1986  Literature            Wole Soyinka\n",
       "341  1985  Literature            Claude Simon\n",
       "347  1984  Literature        Jaroslav Seifert\n",
       "356  1983  Literature         William Golding\n",
       "363  1982  Literature  Gabriel García Márquez\n",
       "373  1981  Literature           Elias Canetti\n",
       "384  1980  Literature          Czeslaw Milosz"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[(nobel['yr'].between(1980, 1989)) & \n",
    "          (nobel['subject']=='Literature'), :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Only Presidents\n",
    "\n",
    "Show all details of the presidential winners:\n",
    "\n",
    "- Theodore Roosevelt\n",
    "- Woodrow Wilson\n",
    "- Jimmy Carter\n",
    "- Barack Obama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>2009</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Barack Obama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>2002</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Jimmy Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>800</th>\n",
       "      <td>1919</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Woodrow Wilson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>862</th>\n",
       "      <td>1906</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Theodore Roosevelt</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr subject              winner\n",
       "78   2009   Peace        Barack Obama\n",
       "160  2002   Peace        Jimmy Carter\n",
       "800  1919   Peace      Woodrow Wilson\n",
       "862  1906   Peace  Theodore Roosevelt"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[nobel['winner'].isin(\n",
    "    ['Theodore Roosevelt', 'Woodrow Wilson', \n",
    "     'Jimmy Carter', 'Barack Obama']), :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. John\n",
    "\n",
    "Show the winners with first name John"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>John O'Keefe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>John B. Gurdon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>John C. Mather</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>John L. Hall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>John B. Fenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>John E. Sulston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>John Pople</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>John Hume</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>213</th>\n",
       "      <td>John E. Walker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>249</th>\n",
       "      <td>John C. Harsanyi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>250</th>\n",
       "      <td>John F. Nash Jr.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>329</th>\n",
       "      <td>John C. Polanyi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>366</th>\n",
       "      <td>John R. Vane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>John H. van Vleck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>John Cornforth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>John R. Hicks</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>John Bardeen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>John C. Kendrew</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>556</th>\n",
       "      <td>John Steinbeck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>602</th>\n",
       "      <td>John Bardeen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>John F. Enders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>635</th>\n",
       "      <td>John Cockcroft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>663</th>\n",
       "      <td>John H. Northrop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>669</th>\n",
       "      <td>John R. Mott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>729</th>\n",
       "      <td>John Galsworthy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>780</th>\n",
       "      <td>John Macleod</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                winner\n",
       "16        John O'Keefe\n",
       "40      John B. Gurdon\n",
       "113     John C. Mather\n",
       "126       John L. Hall\n",
       "151       John B. Fenn\n",
       "159    John E. Sulston\n",
       "200         John Pople\n",
       "206          John Hume\n",
       "213     John E. Walker\n",
       "249   John C. Harsanyi\n",
       "250   John F. Nash Jr.\n",
       "329    John C. Polanyi\n",
       "366       John R. Vane\n",
       "423  John H. van Vleck\n",
       "433     John Cornforth\n",
       "473      John R. Hicks\n",
       "477       John Bardeen\n",
       "554    John C. Kendrew\n",
       "556     John Steinbeck\n",
       "602       John Bardeen\n",
       "612     John F. Enders\n",
       "635     John Cockcroft\n",
       "663   John H. Northrop\n",
       "669       John R. Mott\n",
       "729    John Galsworthy\n",
       "780       John Macleod"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[nobel['winner'].str.startswith('John'), ['winner']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Chemistry and Physics from different years\n",
    "\n",
    "**Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>1984</td>\n",
       "      <td>Chemistry</td>\n",
       "      <td>Bruce Merrifield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>1980</td>\n",
       "      <td>Physics</td>\n",
       "      <td>James Cronin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>1980</td>\n",
       "      <td>Physics</td>\n",
       "      <td>Val Fitch</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr    subject            winner\n",
       "345  1984  Chemistry  Bruce Merrifield\n",
       "389  1980    Physics      James Cronin\n",
       "390  1980    Physics         Val Fitch"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[((nobel['subject']=='Physics') & (nobel['yr']==1980)) |\n",
    "          ((nobel['subject']=='Chemistry') & (nobel['yr']==1984)),\n",
    "         ['yr', 'subject', 'winner']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Exclude Chemists and Medics\n",
    "\n",
    "**Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>1980</td>\n",
       "      <td>Economics</td>\n",
       "      <td>Lawrence R. Klein</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>384</th>\n",
       "      <td>1980</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Czeslaw Milosz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>1980</td>\n",
       "      <td>Peace</td>\n",
       "      <td>Adolfo Pérez Esquivel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>1980</td>\n",
       "      <td>Physics</td>\n",
       "      <td>James Cronin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>1980</td>\n",
       "      <td>Physics</td>\n",
       "      <td>Val Fitch</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr     subject                 winner\n",
       "383  1980   Economics      Lawrence R. Klein\n",
       "384  1980  Literature         Czeslaw Milosz\n",
       "388  1980       Peace  Adolfo Pérez Esquivel\n",
       "389  1980     Physics           James Cronin\n",
       "390  1980     Physics              Val Fitch"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[(nobel['yr']==1980) & ~ nobel['subject'].isin(['Chemistry', 'Medicine']), \n",
    "          ['yr', 'subject', 'winner']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Early Medicine, Late Literature\n",
    "\n",
    "Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Svetlana Alexievich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2014</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Patrick Modiano</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2013</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Alice Munro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2012</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Mo Yan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>2011</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Tomas Tranströmer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2010</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Mario Vargas Llosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>2009</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Herta Müller</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2008</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Jean-Marie Gustave Le Clézio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>2007</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Doris Lessing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>2006</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Orhan Pamuk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>2005</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Harold Pinter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>2004</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Elfriede Jelinek</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>840</th>\n",
       "      <td>1909</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Theodor Kocher</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>847</th>\n",
       "      <td>1908</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Paul Ehrlich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>848</th>\n",
       "      <td>1908</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Ilya Mechnikov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>854</th>\n",
       "      <td>1907</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Alphonse Laveran</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>860</th>\n",
       "      <td>1906</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Camillo Golgi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>861</th>\n",
       "      <td>1906</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Santiago Ramón y Cajal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>866</th>\n",
       "      <td>1905</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Robert Koch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>872</th>\n",
       "      <td>1904</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Ivan Pavlov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>877</th>\n",
       "      <td>1903</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Niels Ryberg Finsen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>884</th>\n",
       "      <td>1902</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Ronald Ross</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>891</th>\n",
       "      <td>1901</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Emil von Behring</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr     subject                        winner\n",
       "4    2015  Literature           Svetlana Alexievich\n",
       "14   2014  Literature               Patrick Modiano\n",
       "29   2013  Literature                   Alice Munro\n",
       "39   2012  Literature                        Mo Yan\n",
       "48   2011  Literature             Tomas Tranströmer\n",
       "64   2010  Literature            Mario Vargas Llosa\n",
       "74   2009  Literature                  Herta Müller\n",
       "86   2008  Literature  Jean-Marie Gustave Le Clézio\n",
       "98   2007  Literature                 Doris Lessing\n",
       "108  2006  Literature                   Orhan Pamuk\n",
       "120  2005  Literature                 Harold Pinter\n",
       "133  2004  Literature              Elfriede Jelinek\n",
       "840  1909    Medicine                Theodor Kocher\n",
       "847  1908    Medicine                  Paul Ehrlich\n",
       "848  1908    Medicine                Ilya Mechnikov\n",
       "854  1907    Medicine              Alphonse Laveran\n",
       "860  1906    Medicine                 Camillo Golgi\n",
       "861  1906    Medicine        Santiago Ramón y Cajal\n",
       "866  1905    Medicine                   Robert Koch\n",
       "872  1904    Medicine                   Ivan Pavlov\n",
       "877  1903    Medicine           Niels Ryberg Finsen\n",
       "884  1902    Medicine                   Ronald Ross\n",
       "891  1901    Medicine              Emil von Behring"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[((nobel['yr']<1910) & (nobel['subject']=='Medicine')) |\n",
    "         ((nobel['yr']>=2004) & (nobel['subject']=='Literature')),\n",
    "         ['yr', 'subject', 'winner']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Umlaut\n",
    "\n",
    "Find all details of the prize won by PETER GRÜNBERG\n",
    "\n",
    "> _Non-ASCII characters_   \n",
    "> The u in his name has an umlaut. You may find this link useful <https://en.wikipedia.org/wiki/%C3%9C#Keyboarding>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>2007</td>\n",
       "      <td>Physics</td>\n",
       "      <td>Peter Grünberg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr  subject          winner\n",
       "105  2007  Physics  Peter Grünberg"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[nobel['winner'].str.upper()=='PETER GRÜNBERG', :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Apostrophe\n",
    "\n",
    "Find all details of the prize won by EUGENE O'NEILL\n",
    "\n",
    "> _Escaping single quotes_   \n",
    "> You can't put a single quote in a quote string directly. You can use two single quotes within a quoted string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>706</th>\n",
       "      <td>1936</td>\n",
       "      <td>Literature</td>\n",
       "      <td>Eugene O'Neill</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       yr     subject          winner\n",
       "706  1936  Literature  Eugene O'Neill"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nobel.loc[nobel['winner'].str.upper()=='EUGENE O\\'NEILL', :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. Knights of the realm\n",
    "\n",
    "Knights in order\n",
    "\n",
    "**List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "      <th>yr</th>\n",
       "      <th>subject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Sir Martin J. Evans</td>\n",
       "      <td>2007</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Sir Peter Mansfield</td>\n",
       "      <td>2003</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>Sir Paul Nurse</td>\n",
       "      <td>2001</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>Sir Harold Kroto</td>\n",
       "      <td>1996</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>311</th>\n",
       "      <td>Sir James W. Black</td>\n",
       "      <td>1988</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>Sir Arthur Lewis</td>\n",
       "      <td>1979</td>\n",
       "      <td>Economics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>422</th>\n",
       "      <td>Sir Nevill F. Mott</td>\n",
       "      <td>1977</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>Sir Bernard Katz</td>\n",
       "      <td>1970</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>Sir John Eccles</td>\n",
       "      <td>1963</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>570</th>\n",
       "      <td>Sir Frank Macfarlane Burnet</td>\n",
       "      <td>1960</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>596</th>\n",
       "      <td>Sir Cyril Hinshelwood</td>\n",
       "      <td>1956</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>Sir Robert Robinson</td>\n",
       "      <td>1947</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>Sir Alexander Fleming</td>\n",
       "      <td>1945</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>675</th>\n",
       "      <td>Sir Howard Florey</td>\n",
       "      <td>1945</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>707</th>\n",
       "      <td>Sir Henry Dale</td>\n",
       "      <td>1936</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>725</th>\n",
       "      <td>Sir Norman Angell</td>\n",
       "      <td>1933</td>\n",
       "      <td>Peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>731</th>\n",
       "      <td>Sir Charles Sherrington</td>\n",
       "      <td>1932</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>743</th>\n",
       "      <td>Sir Venkata Raman</td>\n",
       "      <td>1930</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>748</th>\n",
       "      <td>Sir Frederick Hopkins</td>\n",
       "      <td>1929</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>770</th>\n",
       "      <td>Sir Austen Chamberlain</td>\n",
       "      <td>1925</td>\n",
       "      <td>Peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>869</th>\n",
       "      <td>Sir William Ramsay</td>\n",
       "      <td>1904</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          winner    yr    subject\n",
       "100          Sir Martin J. Evans  2007   Medicine\n",
       "146          Sir Peter Mansfield  2003   Medicine\n",
       "173               Sir Paul Nurse  2001   Medicine\n",
       "224             Sir Harold Kroto  1996  Chemistry\n",
       "311           Sir James W. Black  1988   Medicine\n",
       "393             Sir Arthur Lewis  1979  Economics\n",
       "422           Sir Nevill F. Mott  1977    Physics\n",
       "490             Sir Bernard Katz  1970   Medicine\n",
       "546              Sir John Eccles  1963   Medicine\n",
       "570  Sir Frank Macfarlane Burnet  1960   Medicine\n",
       "596        Sir Cyril Hinshelwood  1956  Chemistry\n",
       "655          Sir Robert Robinson  1947  Chemistry\n",
       "674        Sir Alexander Fleming  1945   Medicine\n",
       "675            Sir Howard Florey  1945   Medicine\n",
       "707               Sir Henry Dale  1936   Medicine\n",
       "725            Sir Norman Angell  1933      Peace\n",
       "731      Sir Charles Sherrington  1932   Medicine\n",
       "743            Sir Venkata Raman  1930    Physics\n",
       "748        Sir Frederick Hopkins  1929   Medicine\n",
       "770       Sir Austen Chamberlain  1925      Peace\n",
       "869           Sir William Ramsay  1904  Chemistry"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.loc[nobel['winner'].str.startswith('Sir'), \n",
    "           ['winner', 'yr', 'subject']]\n",
    "      .sort_values(by=['yr', 'winner'], ascending=[False, True]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14. Chemistry and Physics last\n",
    "\n",
    "The expression **subject IN ('Chemistry','Physics')** can be used as a value - it will be 0 or 1.\n",
    "\n",
    "**Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>winner</th>\n",
       "      <th>subject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>Richard Stone</td>\n",
       "      <td>Economics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347</th>\n",
       "      <td>Jaroslav Seifert</td>\n",
       "      <td>Literature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>350</th>\n",
       "      <td>César Milstein</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>349</th>\n",
       "      <td>Georges J.F. Köhler</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348</th>\n",
       "      <td>Niels K. Jerne</td>\n",
       "      <td>Medicine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>351</th>\n",
       "      <td>Desmond Tutu</td>\n",
       "      <td>Peace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>Bruce Merrifield</td>\n",
       "      <td>Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>352</th>\n",
       "      <td>Carlo Rubbia</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>353</th>\n",
       "      <td>Simon van der Meer</td>\n",
       "      <td>Physics</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  winner     subject\n",
       "346        Richard Stone   Economics\n",
       "347     Jaroslav Seifert  Literature\n",
       "350       César Milstein    Medicine\n",
       "349  Georges J.F. Köhler    Medicine\n",
       "348       Niels K. Jerne    Medicine\n",
       "351         Desmond Tutu       Peace\n",
       "345     Bruce Merrifield   Chemistry\n",
       "352         Carlo Rubbia     Physics\n",
       "353   Simon van der Meer     Physics"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(nobel.assign(flg=nobel['subject'].isin(['Chemistry', 'Physics']))\n",
    "      .loc[(nobel['yr']==1984), :]\n",
    "      .sort_values(by=['flg', 'subject', 'winner'])\n",
    "      .loc[:, ['winner', 'subject']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
